﻿using System;
using System.Collections.Generic;
using System.Collections;

namespace Diary.BAL
{
    public class Content
    {
        const string TableName = "Content";

        /// <summary>
        /// 向数据库中插入Category数据，如成功返回新记录
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        static public Model.Content Insert(Model.Content model)
        {
            string sql = string.Format("insert into {0} (Title, SubTitle, Type, Summary, MainBody, CategoryId, CreateTime, RealTime, ModifyTime, Description, Remark) values ('{1}', '{2}', '{3}', '{4}', '{5}', {6}, '{7}', '{8}', '{9}', '{10}', '{11}')",
                    TableName, model.Title, model.SubTitle, model.Type, model.Summary, model.MainBody, model.CategoryId, model.CreateTime, model.RealTime, model.ModifyTime, model.Description, model.Remark);
            model.Id = DBController.Default.executeReturnLastId(sql);
            return model;
        }

        /// <summary>
        /// 删除指定ID的记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        static public bool Delete(int id)
        {
            string sql = string.Format("delete from {0} where Id={1}", TableName, id);
            return DBController.Default.executeNoReturn(sql);
        }


        /// <summary>
        /// 修改指定ID的记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        static public bool Modify(Model.Content model)
        {
            string sql = string.Format("update {0} set Title='{2}', SubTitle='{3}', Type='{4}', Summary='{5}', MainBody='{6}', CategoryId={7}, CreateTime='{8}', RealTime='{9}', ModifyTime='{10}', Description='{11}', Remark='{12}' where Id={1}",
                    TableName, model.Id, model.Title, model.SubTitle, model.Type, model.Summary, model.MainBody, model.CategoryId, model.CreateTime, model.RealTime, model.ModifyTime, model.Description, model.Remark);
            return DBController.Default.executeNoReturn(sql);
        }


        static List<Model.Content> query_by_sql(string sql)
        {
            List<Hashtable> results = DBController.Default.query(sql);
            List<Model.Content> history = new List<Model.Content>();
            foreach (Hashtable result in results)
            {
                Model.Content model = new Model.Content();
                model.Id = result.toInt("Id");
                model.Title = result.toString("Title");
                model.SubTitle = result.toString("SubTitle");
                model.Type = result.toString("Type");
                model.Summary = result.toString("Summary");
                model.MainBody = result.toString("MainBody");
                model.CategoryId = result.toInt("CategoryId");
                model.CreateTime = result.toDateTime("CreateTime");
                model.RealTime = result.toDateTime("RealTime");
                model.ModifyTime = result.toDateTime("ModifyTime");
                model.Description = result.toString("Description");
                model.Remark = result.toString("Remark");
                history.Add(model);
            }

            return history;
        }

        static public Model.Content query_by_id(int id)
        {
            string sql = string.Format("select * from {0} where id={1} limit 1", TableName, id);
            List<Model.Content> contents = query_by_sql(sql);
            if (contents.Count == 0) return null;
            return contents[0];
        }

        static public List<Model.Content> query(int? categoryId)
        {
            string sql = string.Format("select * from {0} where 1=1", TableName);
            if (categoryId.HasValue && categoryId.Value >= 0)
            {
                sql += string.Format(" and CategoryId={0}", categoryId.Value);
            }
            return query_by_sql(sql);
        }

        /// <summary>
        /// 返回所有记录
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        static public List<Model.Content> query_all()
        {
            return query(null);
        }

        static public List<Model.Content> query_by_categoryId(int categoryId)
        {
            return query(categoryId);
        }
    }
}
